/**
 * Created by Administrator on 2017/1/5.
 */
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;

import java.util.ArrayList;



public class store {
    public  static Connection getConn()throws  Exception {//建立连接
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/bilibili?useUnicode=true&characterEncoding=utf-8&useSSL=false";
        String username = "root";
        String password = "weirui199748";
        Connection conn = null;
        try {
            Class.forName(driver);//加载对应驱动
            conn = (Connection) DriverManager.getConnection(url, username, password);
        }
        catch (Exception e){
            System.out.println("数据库连接失败");
            e.printStackTrace();
        }


        return conn;
    }
    public static void createtable(int category)throws Exception{
        Connection conn=getConn();
        String sql="create table category"+category+"(aid integer not null unique,author text not null,favorites integer not null,category text not null)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.executeUpdate();
            pstmt.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        conn.close();
    }
    public static void insert(ArrayList<information> informaions ,int category) throws  Exception{
        createtable(category);
        Connection conn=getConn();
        for(information s:informaions) {
            String sql = "insert into category"+category+"(aid,author,favorites,category) values(?,?,?,?)";
            PreparedStatement pstmt;
            try {
                pstmt = (PreparedStatement) conn.prepareStatement(sql);
                pstmt.setInt(1,s.aid);
                pstmt.setString(2,s.author);
                pstmt.setInt(3,s.favorites);
                pstmt.setString(4,s.category);
                pstmt.executeUpdate();
                pstmt.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        conn.close();
    }
    public static String[] getAVnumberbycategory(int category)throws Exception{
        Connection conn=getConn();
        String sql="select aid from category"+category+" order by favorites desc limit 3";
        PreparedStatement preparedStatement=(PreparedStatement)conn.prepareStatement(sql);
        ResultSet resultSet=preparedStatement.executeQuery();
        String[] strings=new String[3];
        strings[0]=resultSet.getString(0);
        strings[1]=resultSet.getString(1);
        strings[2]=resultSet.getString(2);
        preparedStatement.close();
        return strings;
    }
    public static void main(String[] args)throws  Exception{
        createtable(1);
    }
}
